package com.wframe.project.former.dataservices.coupon;

import com.frame.database.JBaseDao;
import com.frame.database.JdataBean;
import com.frame.database.PageResult;
import com.frame.util.DateConvert;
import org.apache.commons.lang.StringUtils;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

public class CouponDao extends JBaseDao {

    public CouponDao() {
        super("wb_coupon");
    }

    public void addSales(String id, int addsales) throws Exception {
        getSession().update("update wb_coupon set sales=sales+" + addsales + " where id=?", new Object[]{id});
    }

    public void addUsenum(String id, int addusenum) throws Exception {
        getSession().update("update wb_coupon set usenum=usenum+" + addusenum + " where id=?", new Object[]{id});
    }

    public void saveCoupon(Map dataMap) throws Exception {
        getSession().save("wb_coupon", dataMap);
    }

    public PageResult queryPage(JdataBean parms, int size, int pageno) throws Exception {
        String sql = "SELECT " +
                " coup.id, " +
                " cast( " +
                "  coup.receive = 1 " +
                "  AND coup.amount > coup.sales " +
                "  AND NOW() BETWEEN coup.receive_starttime " +
                "  AND coup.receive_endtime as char " +
                " ) receive, " +
                " coup.`name`, " +
                " coup.source, " +
                " coup.startdate, " +
                " coup.enddate, " +
                " coup.activity_day, " +
                " coup.min_money, " +
                " coup.offer_money, " +
                " coup.sales, " +
                " coup.usenum, " +
                " CAST(NOW() < coup.enddate AS char) available, " +
                " CASE prom.product_type " +
                "WHEN 'o' THEN " +
                " '全部商品' " +
                "WHEN '1' THEN " +
                " prod.`name` " +
                "WHEN '2' THEN " +
                " '部分商品' " +
                "WHEN '3' THEN " +
                " wpc.`name` " +
                "END rang " +
                "FROM " +
                " wb_coupon coup " +
                "JOIN wb_promotions prom ON coup.promotionid = prom.id " +
                "LEFT JOIN wb_products prod ON (prom.product_val = prod.id) " +
                "LEFT JOIN wb_products_class wpc ON (prom.product_val = wpc.id) " +
                "WHERE " +
                " coup.tag = 0 " +
                "AND coup.state = 1 " +
                "AND (prom.product_type=0 or " +
                " ( " +
                "  prom.product_type = 1 " +
                "  AND prom.product_val = prod.id " +
                " ) " +
                " OR ( " +
                "  prom.product_type = 3 " +
                "  AND prom.product_val = wpc.id " +
                " ) " +
                ") ";
        List parmlist = new ArrayList();
        if (parms != null) {
            if (StringUtils.isNotBlank(parms.getString("name"))) {
                sql += " and coup.name like ?";
                parmlist.add("%" + parms.getString("name") + "%");
            }
            String platform = parms.getString("platform");
            if (StringUtils.isNotBlank(platform)) {
                if ("0".equalsIgnoreCase(platform)) {
                    sql += " and (coup.source is null or coup.source = ? ) ";
                } else {
                    sql += " and coup.source = ?";
                }
                parmlist.add(platform);
            }

            String state = parms.getString("state");
            if (StringUtils.isNotBlank(state)) {
                if ("0".equalsIgnoreCase(state)) {
                    sql += " and coup.enddate<?";
                } else {
                    sql += " and coup.enddate>=?";
                }
                parmlist.add(new Date());
            }
        }
        sql += "order by coup.id desc";
        return getSession().query2(sql, parmlist, size, pageno);
    }

    public JdataBean getCoupon(String id) throws Exception {
        String sql = "SELECT coup.*,prom.product_type,prom.product_val " +
                "from wb_coupon coup " +
                "join wb_promotions prom on coup.promotionid=prom.id " +
                "where coup.id=?";
        return getSession().getObject(sql, new Object[]{id});
    }

    public PageResult queryCouponDetails(JdataBean parms, int pageSize, int pageNum) throws Exception {
        List parmlist = new ArrayList();
        return getSession().query2(getCouponDetailsSql(parms, parmlist), parmlist, pageSize, pageNum);
    }

    public List<JdataBean> queryCouponDetails(JdataBean parms) throws Exception {
        List parmlist = new ArrayList();
        return getSession().query2(getCouponDetailsSql(parms, parmlist), parmlist);
    }

    private String getCouponDetailsSql(JdataBean parms, List parmlist) {
        String sql = "SELECT coup.get_source,coup.create_time,coup.use_time,cast(coup.use_time is not null as char) tag,wu.title,wu.phone,comp.`name` from wb_coupon coup " +
                "JOIN wbuser wu on coup.userid=wu.id " +
                "JOIN wb_company comp on wu.id=comp.userid " +
                "WHERE coup.tag=1";
        if (parms != null) {
            String source = parms.getString("get_source");
            if (StringUtils.isNotBlank(source)) {
                if ("2".equalsIgnoreCase(source)) {
                    sql += " and (coup.get_source is null or coup.get_source=?)";
                } else {
                    sql += " and coup.get_source=?";
                }
                parmlist.add(source);
            }

            String tag = parms.getString("tag");
            if (StringUtils.isNotBlank(tag)) {
                if ("0".equalsIgnoreCase(tag)) {
                    sql += " and coup.use_time is null";
                } else {
                    sql += " and coup.use_time is not null";
                }
            }
            if (StringUtils.isNotBlank(parms.getString("starttime"))) {
                sql += " and coup.create_time>=?";
                parmlist.add(DateConvert.toDate(parms.getString("starttime") + " 00:00:00"));
            }
            if (StringUtils.isNotBlank(parms.getString("endtime"))) {
                sql += " and coup.create_time<=?";
                parmlist.add(DateConvert.toDate(parms.getString("endtime") + " 23:59:59"));
            }
        }
        sql += " order by coup.id desc";
        return sql;
    }
}
